﻿using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;

namespace ResearchRepository.Models
{
    public class DatabaseManager
    {

        private MySqlConnection _connection;

        public DatabaseManager(string connectionString)
        {
            //TODO: fill in connection string for local database
            connectionString = "DATA Source = localhost; Database = EduRepoTest; user id=root; password=leilani7";
            Connection = new MySqlConnection(connectionString);
        }

        public void Dispose()
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
                Connection.Dispose();
            }
        }

        private MySqlConnection Connection
        {
            get
            {
                try
                {
                    if (_connection != null && _connection.State == ConnectionState.Closed)
                    {
                        _connection.Open();
                    }

                    return _connection;
                }
                catch (MySqlException e)
                {

                    throw;
                }
            }

            set { _connection = value; }
        }

        /// <summary>
        /// selects all projects from database
        /// </summary>
        /// <returns>all Projects in database</returns>
        public Dictionary<string, Project> GetProjectData()
        {
            string procedureText =
                "SELECT PROJECT_NAME, PROJECT_TYPE, PROJECT_OWNER  FROM PROJECTDATA NATURAL JOIN PROJECTOWNERS";

            //performs select and gets list of DALProjectData from database
            var result = GetData(ExecuteReader(CreateCommand(procedureText, CommandType.Text, null)),
                                 CreateProjectData);

            return BuildProjectObject(new List<DALProjectData>(result));
        }

        //public void DeleteProject(string projectName)
        //{
        //    string procedureText = String.Format("DELETE FROM PROJECTDATA WHERE PROJECT_NAME = '{0}';",
        //                                         projectName);
        //    var executeReader = ExecuteReader(CreateCommand(procedureText, CommandType.Text, null));

            
        //    procedureText = String.Format("DELETE FROM PROJECTOWNERS WHERE PROJECT_NAME = '{0}';",
        //                                         projectName);
        //    executeReader = ExecuteReader(CreateCommand(procedureText, CommandType.Text, null));
        //}

        /// <summary>
        /// Creates dictionary with Project objects
        /// </summary>
        /// <param name="projectDataList">a list of DALProjectDatas to be converted to Project objects</param>
        /// <returns>dictionary of Projects keyed by Project Name</returns>
        private static Dictionary<string, Project> BuildProjectObject(IEnumerable<DALProjectData> projectDataList)
        {
            var projectDataDictionary = new Dictionary<string, Project>();

            //iterate through DALProjectDatas and set Project properties to equivalent fields
            //add Projects to dictionary
            foreach (var projectData in projectDataList)
            {
                Project project;

                project = new Project
                {
                    ProjectName = projectData.ProjectName,
                    ProjectOwner = projectData.ProjectOwner,
                    ProjectType = projectData.ProjectType
                };

                projectDataDictionary.Add(project.ProjectName, project);

            }

            return projectDataDictionary;
        }

        /// <summary>
        /// Gets properties from a single record from database, sets to properties of DALProjectData
        /// </summary>
        /// <param name="record">single database row</param>
        /// <returns>DALProjectData representaiton of row</returns>
        private static DALProjectData CreateProjectData(IDataRecord record)
        {
            return new DALProjectData
            {
                ProjectName = record["PROJECT_NAME"].ToString(),
                ProjectOwner = record["PROJECT_OWNER"].ToString(),
                ProjectType = record["PROJECT_TYPE"].ToString()
            };
        }

        /// <summary>
        /// Creates MySqlCommand
        /// </summary>
        /// <param name="command">command text</param>
        /// <param name="commandType">command type</param>
        /// <param name="mySqlParameters">command paramaters</param>
        /// <returns>complete MySqlCommand</returns>
        private MySqlCommand CreateCommand(string command, CommandType commandType, IEnumerable<MySqlParameter> mySqlParameters)
        {
            var mySqlCommand = new MySqlCommand(command, Connection);
            mySqlCommand.CommandType = commandType;

            if (mySqlParameters != null)
            {
                foreach (var param in mySqlParameters)
                {
                    mySqlCommand.Parameters.Add(param);
                }
            }

            return mySqlCommand;
        }

        /// <summary>
        /// Creates a reader for the given command
        /// </summary>
        /// <param name="command">command to be executed</param>
        /// <returns></returns>
        private static IDataReader ExecuteReader(IDbCommand command)
        {
            try
            {
                return command.ExecuteReader();
            }
            catch (InvalidOperationException invalidOperationException)
            {
                throw new Exception(invalidOperationException.Message, invalidOperationException.InnerException);
            }
            catch (MySqlException MyqlException)
            {
                throw new Exception(MyqlException.Message, MyqlException.InnerException);
            }
        }

        /// <summary>
        /// Takes an object type which uses a reader and build a class representation of the data
        /// </summary>
        /// <typeparam name="T">The type of object that represents that database data</typeparam>
        /// <param name="reader">The reader object</param>
        /// <param name="buildObject">The object that will be used to hold that reader data</param>
        /// <returns>An enumerable object to represent the reader data</returns>
        private static IEnumerable<T> GetData<T>(IDataReader reader, Func<IDataRecord, T> buildObject)
        {
            try
            {
                if (!reader.IsClosed)
                {
                    while (reader.Read())
                    {
                        yield return buildObject(reader);
                    }
                }
            }
            finally
            {
                reader.Dispose();
            }
        }
    }
}